import { pool } from "server/database/config";
import { fileUploadService } from "server/utils/fileUpload";

export interface Project {
  id: number;
  title: string;
  description: string;
  content: string;
  status: "published" | "draft";
  is_featured: boolean;
  category: string;
  technologies: string[];
  cover_image_id: number;
  cover_image_url: string; // 完整的封面图片URL
  view_count: number;
  like_count: number;
  demo_url?: string;
  github_url?: string;
  role?: string;
  created_at: string;
  updated_at: string;
}

export interface CreateProjectData {
  title: string;
  description: string;
  content: string;
  status: "published" | "draft";
  is_featured: boolean;
  category: string;
  technologies: string[];
  cover_image_id: number;
  demo_url?: string;
  github_url?: string;
  role?: string;
}

export interface UpdateProjectData {
  title?: string;
  description?: string;
  content?: string;
  status?: "published" | "draft";
  is_featured?: boolean;
  category?: string;
  technologies?: string[];
  cover_image_id: number;
  demo_url?: string;
  github_url?: string;
  role?: string;
  view_count?: number;
  like_count?: number;
}

export class Projects {
  // 获取所有项目
  static async getProjects(
    params: {
      page?: number;
      pageSize?: number;
      search?: string;
      status?: string;
      category?: string;
      featured?: string;
      sortBy?: string;
      sortOrder?: string;
    } = {}
  ): Promise<{ projects: Project[]; total: number }> {
    const {
      page = 1,
      pageSize = 10,
      search,
      status,
      category,
      featured,
      sortBy = "created_at",
      sortOrder = "desc",
    } = params;

    // 确保参数是正确的类型
    const pageNum = Math.max(1, parseInt(String(page)) || 1);
    const pageSizeNum = Math.min(
      Math.max(1, parseInt(String(pageSize)) || 10),
      50
    );

    let whereClause = "WHERE 1=1";
    let dataWhereClause = "WHERE 1=1";
    const values: any[] = [];

    if (search && typeof search === "string" && search.trim()) {
      whereClause += " AND (title LIKE ? OR description LIKE ?)";
      dataWhereClause += " AND (p.title LIKE ? OR p.description LIKE ?)";
      const searchTerm = `%${search.trim()}%`;
      values.push(searchTerm, searchTerm);
    }

    if (status && typeof status === "string" && status.trim()) {
      whereClause += " AND status = ?";
      dataWhereClause += " AND p.status = ?";
      values.push(status.trim());
    }

    if (category && typeof category === "string" && category.trim()) {
      whereClause += " AND category = ?";
      dataWhereClause += " AND p.category = ?";
      values.push(category.trim());
    }

    if (featured && typeof featured === "string") {
      whereClause += " AND is_featured = ?";
      dataWhereClause += " AND p.is_featured = ?";
      values.push(featured === "true" ? 1 : 0);
    }

    const validSortFields = [
      "created_at",
      "updated_at",
      "view_count",
      "like_count",
      "title",
    ];
    const validSortOrder = ["asc", "desc"];

    const sortField = validSortFields.includes(sortBy) ? sortBy : "created_at";
    const order = validSortOrder.includes(sortOrder.toLowerCase())
      ? sortOrder.toLowerCase()
      : "desc";

    try {
      // 获取总数
      const countQuery = `SELECT COUNT(*) as total FROM projects ${whereClause}`;

      const [countResult]: any = await pool.execute(countQuery, values);
      const total = parseInt(countResult[0].total) || 0;

      // 获取分页数据
      const offset = (pageNum - 1) * pageSizeNum;
      const query = `SELECT p.*
                     FROM projects p
                     ${dataWhereClause} 
                     ORDER BY ${sortField} ${order} 
                     LIMIT ${pageSizeNum} OFFSET ${offset}`;

      const [rows]: any = await pool.execute(query, values);

      // 转换JSON字段
      const projects = await Promise.all(
        rows.map(async (row: any) => {
          let technologies = [];

          // 安全地解析 technologies 字段
          if (row.technologies) {
            try {
              // 如果是字符串，尝试解析为JSON
              if (typeof row.technologies === "string") {
                // 检查是否是JSON格式
                if (
                  row.technologies.startsWith("[") ||
                  row.technologies.startsWith("{")
                ) {
                  technologies = JSON.parse(row.technologies);
                } else {
                  // 如果不是JSON格式，按逗号分割
                  technologies = row.technologies
                    .split(",")
                    .map((tech: string) => tech.trim())
                    .filter(Boolean);
                }
              } else if (Array.isArray(row.technologies)) {
                technologies = row.technologies;
              }
            } catch (error) {
              console.warn(
                `解析 technologies 字段失败 (项目ID: ${row.id}):`,
                error
              );
              // 如果JSON解析失败，尝试按逗号分割
              if (typeof row.technologies === "string") {
                technologies = row.technologies
                  .split(",")
                  .map((tech: string) => tech.trim())
                  .filter(Boolean);
              }
            }
          }

          // 生成封面图片URL
          let cover_image_url = "";
          if (row.cover_image_id) {
            cover_image_url = await fileUploadService.generateFileUrlById(
              row.cover_image_id
            );
          }

          return {
            ...row,
            technologies,
            is_featured: Boolean(row.is_featured),
            view_count: parseInt(row.view_count) || 0,
            like_count: parseInt(row.like_count) || 0,
            cover_image_url,
          };
        })
      );

      return { projects, total };
    } catch (error) {
      console.error("Database query error in getProjects:", error);
      throw new Error(
        `数据库查询失败: ${error instanceof Error ? error.message : "未知错误"}`
      );
    }
  }

  // 根据ID获取项目
  static async getProjectById(id: number): Promise<Project | null> {
    const [rows]: any = await pool.execute(
      `SELECT p.* 
       FROM projects p 
       WHERE p.id = ?`,
      [id]
    );

    if (rows.length === 0) return null;

    const project = rows[0];

    // 安全地解析 technologies 字段
    let technologies = [];
    if (project.technologies) {
      try {
        if (typeof project.technologies === "string") {
          if (
            project.technologies.startsWith("[") ||
            project.technologies.startsWith("{")
          ) {
            technologies = JSON.parse(project.technologies);
          } else {
            technologies = project.technologies
              .split(",")
              .map((tech: string) => tech.trim())
              .filter(Boolean);
          }
        } else if (Array.isArray(project.technologies)) {
          technologies = project.technologies;
        }
      } catch (error) {
        console.warn(
          `解析 technologies 字段失败 (项目ID: ${project.id}):`,
          error
        );
        if (typeof project.technologies === "string") {
          technologies = project.technologies
            .split(",")
            .map((tech: string) => tech.trim())
            .filter(Boolean);
        }
      }
    }

    // 生成封面图片URL
    let cover_image_url = "";
    if (project.cover_image_id) {
      cover_image_url = await fileUploadService.generateFileUrlById(
        project.cover_image_id
      );
    }

    return {
      ...project,
      technologies,
      is_featured: Boolean(project.is_featured),
      view_count: parseInt(project.view_count) || 0,
      like_count: parseInt(project.like_count) || 0,
      cover_image_id: project.cover_image_id,
      cover_image_url,
    };
  }

  // 创建项目
  static async createProject(data: CreateProjectData): Promise<number> {
    const [result]: any = await pool.execute(
      `INSERT INTO projects (title, description, content, status, is_featured, category, technologies, cover_image_id, demo_url, github_url, role) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        data.title,
        data.description,
        data.content,
        data.status,
        data.is_featured,
        data.category,
        JSON.stringify(data.technologies),
        data.cover_image_id || null,
        data.demo_url || null,
        data.github_url || null,
        data.role || null,
      ]
    );

    return result.insertId;
  }

  // 更新项目
  static async updateProject(
    id: number,
    data: UpdateProjectData
  ): Promise<boolean> {
    const fields: string[] = [];
    const values: any[] = [];

    Object.entries(data).forEach(([key, value]) => {
      if (value !== undefined) {
        if (key === "technologies") {
          fields.push("technologies = ?");
          values.push(JSON.stringify(value));
        } else if (key === "is_featured") {
          fields.push("is_featured = ?");
          values.push(value);
        } else if (key === "cover_image_id") {
          fields.push("cover_image_id = ?");
          values.push(value);
        } else if (key === "demo_url") {
          fields.push("demo_url = ?");
          values.push(value);
        } else if (key === "github_url") {
          fields.push("github_url = ?");
          values.push(value);
        } else if (key === "role") {
          fields.push("role = ?");
          values.push(value);
        } else {
          fields.push(`${key} = ?`);
          values.push(value);
        }
      }
    });

    if (fields.length === 0) return false;

    fields.push("updated_at = CURRENT_TIMESTAMP");

    const [result]: any = await pool.execute(
      `UPDATE projects SET ${fields.join(", ")} WHERE id = ?`,
      [...values, id]
    );

    return result.affectedRows > 0;
  }

  // 删除项目
  static async deleteProject(id: number): Promise<boolean> {
    const [result]: any = await pool.execute(
      "DELETE FROM projects WHERE id = ?",
      [id]
    );

    return result.affectedRows > 0;
  }

  // 增加浏览量
  static async incrementViewCount(id: number): Promise<boolean> {
    const [result]: any = await pool.execute(
      "UPDATE projects SET view_count = view_count + 1 WHERE id = ?",
      [id]
    );

    return result.affectedRows > 0;
  }

  // 获取项目统计
  static async getProjectStats(): Promise<{
    total: number;
    published: number;
    draft: number;
    archived: number;
  }> {
    const [rows]: any = await pool.execute(`
      SELECT 
        COUNT(*) as total,
        SUM(CASE WHEN status = 'published' THEN 1 ELSE 0 END) as published,
        SUM(CASE WHEN status = 'draft' THEN 1 ELSE 0 END) as draft,
        SUM(CASE WHEN status = 'archived' THEN 1 ELSE 0 END) as archived
      FROM projects
    `);

    const stats = rows[0];
    return {
      total: parseInt(stats.total),
      published: parseInt(stats.published),
      draft: parseInt(stats.draft),
      archived: parseInt(stats.archived),
    };
  }
}
